package org.homeorganiser.flex.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.homeorganiser.flex.dto.SubCategory;
import org.homeorganiser.flex.util.Constant;

public class SubCategoryDAO implements IGenericDAO<SubCategory> {

	private final DataSource dataSource;
	Connection conn = null;
	ResultSet rs = null;
	PreparedStatement ps;
	
	public SubCategoryDAO(DataSource dataSource) {
        this.dataSource = dataSource;
    }
	
	public List<SubCategory> findAll(int type) {
		List<SubCategory> subcatList = new ArrayList<SubCategory>();
		
		SubCategory subCat;
		try {
				
			conn = this.dataSource.getConnection();
            ps = conn.prepareStatement(Constant.SQL_SELECT_ALL_SUBCATEGORY);
            ps.setInt(1,type);
            rs = ps.executeQuery();
            
           while(rs.next()){
        	    subCat = new SubCategory();
        	    subCat.setId(rs.getInt("id"));
        	    subCat.setAddedBy(rs.getString("addedBy"));
        	    subCat.setSubCategoryName(rs.getString("subcategoryname"));
        	    subCat.setCategoryId(rs.getInt("categoryid"));
        	    subcatList.add(subCat);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally{
        		try {
        			if(rs != null){
        				rs.close();	
        			}
        			if(ps != null){
        				ps.close();
        			}
            		if(conn != null){
            			conn.close();
            		}
        			
				} catch (SQLException e) {
					e.printStackTrace();
				}
        	
        }
		return subcatList;
	}

	public List<SubCategory> findByName(String name) {
		// TODO Auto-generated method stub
		return null;
	}

	public SubCategory findById(int id) {
		// TODO Auto-generated method stub
		return null;
	}

	public SubCategory create(SubCategory subCat) {
		 try {
	            conn = this.dataSource.getConnection();
	            ps = conn.prepareStatement(Constant.SQL_INSERT_SUBCATEGORY_TABLE,
	                new String[] { "ID" });
	           
	            ps.setString(1, subCat.getSubCategoryName());
	            ps.setInt(2, subCat.getInventorytype());
	            ps.setString(3, subCat.getAddedBy());
	            ps.setInt(4, subCat.getCategoryId());
	            
	            ps.executeUpdate();
	            ResultSet rs = ps.getGeneratedKeys();
	            rs.next();
	            // Update the id in the returned object. This is important as this value must be returned to the client.
	            int id = rs.getInt(1);
	            subCat.setId(id);
	        } catch (Exception e) {
	            e.printStackTrace();
	            throw new RuntimeException(e);
	        }finally{
     		try {
     			if(rs != null){
     				rs.close();	
     			}
     			if(ps != null){
     				ps.close();
     			}
         		if(conn != null){
         			conn.close();
         		}
     			
				} catch (SQLException e) {
					e.printStackTrace();
				}
     	
     }
	        return subCat;
	}

	public boolean update(SubCategory item) {
		// TODO Auto-generated method stub
		return false;
	}

	public boolean remove(SubCategory item) {
		// TODO Auto-generated method stub
		return false;
	}

	public boolean removeAll(int type) {
		try {
			
			conn = this.dataSource.getConnection();
            ps = conn.prepareStatement(Constant.SQL_DELETE_ALL_SUBCATEGORY);
            ps.setInt(1,type);
            return  ps.executeUpdate() == 1;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally{
    		try {
    			if(rs != null){
    				rs.close();	
    			}
    			if(ps != null){
    				ps.close();
    			}
        		if(conn != null){
        			conn.close();
        		}
    			
			} catch (SQLException e) {
				e.printStackTrace();
			}
    	
    }
	}

}
